import java.sql.*;
import java.util.*;
//import java.text.DateFormat;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;


/*************************************************************************
 * Filename: DB.Java
 * Author Ben Johnson
 * Company:  1186 Entertainment
 * Last Modified: 3-17-06
 *
 * Description:
 * This class is the Database interface for the Reversi Game Project.
 * This class seeks to provide connectivity to the database and to provide
 * commonly used actions for operations on the database.  This file is
 * designed to work with the schema provided in the schema.sql file.
 * Also this classes main method provides a test bench for normal database
 * usage and operations.
 *
 * TODO:
 * 	* add javadoc comments
 *  * comment all code better
 *  * provide exception handling with try/catch
 *  * provide toggleable debugging print statements
 *  * allow for variable deliminators
 *
 * Change Log:
 *
 * @author Ben Johnson
 *
 *************************************************************************/
public class DB
{
	//class variables
	public static boolean debug = true;
	public static Connection c = null;

	//instance variables
	/** Statement object used for all of the SQL actions*/
	public Statement stmt = null;
	/** ResultSet to hold the results of queries and updates*/
	public ResultSet rs = null;
	/** result to an action for return variable*/
	public boolean r = false;
	/** rows affected by an update or insert*/
	public int rows = 0;
	/** sql string for statements */
	public String sql = "";

	/**
	 * This is the main method that allows for debugging
	 */
	public static void main(String[] args) throws IOException,SQLException,Exception
	{
		//reads in a text file for the scripting
		BufferedReader kbd = new BufferedReader(new InputStreamReader(System.in));

		int in = 0;
		while(in != 1 && in != 2)
		{
			System.out.print("Enter a 1 to run the Testing and 2 to run a script: ");
			in = Integer.parseInt(kbd.readLine());
		}

		//Runs a series of statements to test all the methods
		if(in == 1)
		{
			//setup a new database instance
			DB testdb = new DB();
			testdb.connect("se18","l13z16o4");
			testdb.usernameIsTaken("bwj");
			testdb.usernameIsTaken("testuser");

			//make a new player to register
			PlayerInfo newplayer = new PlayerInfo();
			GameStats newstats = new GameStats();
			newstats.gameName = "Reversi";
			newstats.wins = 0;
			newstats.losses = 0;
			newstats.forfeits = 0;
			newplayer.id = testdb.getNextUserID();
			newplayer.username = "testuser";
			newplayer.email = "tester@test.com";
			newplayer.password = "password";
			newplayer.gender = "gender";
			//DateFormat format = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.SHORT);
			newplayer.birth = "1960-01-01";
			//newplayer.gameStats = newstats;

			//registers the new player
			testdb.registerUser(newplayer);

			//validates that the username and password is correct
			testdb.validateUser(newplayer.username,newplayer.password);

			//validates that the user is in the gametable
			testdb.isUserInGameTable("reversi_table",newplayer.id);

			//adds the new user to the game table
			testdb.addNewUserToGameTable("reversi_table",newplayer.id);

			//gets a user's profile
			testdb.getUserProfile(testdb.getUserId(newplayer.username));

			//changes some of the users information
			newplayer.email = "newemail@test.com";

			//updates the new info
			testdb.updateUserProfile(newplayer);

			//add a series of wins losses and forfeits
			testdb.addWin("reversi_table",newplayer.id);
			testdb.addWin("reversi_table",newplayer.id);
			testdb.addWin("reversi_table",newplayer.id);
			testdb.addLoss("reversi_table",newplayer.id);
			testdb.addForfeit("reversi_table",newplayer.id);

			//retreives a profile
			testdb.getUserProfile(newplayer.id);

			//gets the High scores
			testdb.getHighScores("reversi_table",5);


			//remove test player from the database
			String sql = "DELETE FROM reversi_table WHERE user_id in (SELECT id FROM user_table WHERE username='"+newplayer.username+"')";
			System.out.println("sql = " +sql);
			int r = testdb.stmt.executeUpdate(sql);
			System.out.println("Removed test player from reversi table: " + r);
			sql = "DELETE FROM user_table WHERE username='"+newplayer.username+"'";
			System.out.println("sql = " +sql);
			r = testdb.stmt.executeUpdate(sql);
			System.out.println("Removed test player from user table: " + r);


			System.out.println("Completed Testing Successfully.");

		}
		//run a sql script file
		else if(in ==2)
		{
			System.out.print("Please enter the name of a script to run: ");
			String filename = kbd.readLine();//get the filename
			File script = new File(filename); // set the file
			DB testdb = new DB(); //make a new DB
			testdb.connect("se18","l13z16o4");
			Script runner = new Script(); //creat a new script runner
			runner.executeScript(c,script,new PrintWriter("test.log")); //execute the script
			System.out.println("Script Sucessfully Run!");
		}
	}

	/**
	 * Default connector to the database interface object using user "se18"
	 * @return whether the connect succeded or not
	 */
	public boolean connect() throws SQLException
	{
		return this.connect("se18","l13z16o4");
	}


    /**
     * connects a player to the server and database with JDBC
     * Main DB to connect to
     *
     * @param username the username of the j7 account
     * @param password the password of the j7 username account
     * @return whether the connect succeded or not
     */
    public boolean connect(String username, String password) throws SQLException
    {
		c = null;
		stmt = null;
		rs = null;
		r = false;
		sql = "";

		System.out.println("Connecting to the DB");
		try {
			//Connect to the database here
			String connectString = "jdbc:oracle:thin:@(description=(address=(host=ragnall.ee.pitt.edu)(protocol=tcp)(port=1521))(connect_data=(sid=j7)))";
			DriverManager.registerDriver(new OracleDriver()); //setup the drivermanager
			c = DriverManager.getConnection(connectString,username,password); //get a new connection
			c.setAutoCommit(true); //set AutoCommit on
			stmt = c.createStatement(); //create instance statement
		}
		catch(Exception e) {
			System.out.println("Error Connecting");
			System.out.println(e);
			System.exit(1);
		}
		System.out.println("Connected");
		return true;
	}

	/**
	 * clears out and loads the default schema into the database
	 * @return whether the load succeded or not
	 */
	public boolean loadSchema() throws SQLException,Exception
	{
		DB testdb = new DB();
		testdb.connect("se18","l13z16o4");
		Script runner = new Script();
		runner.executeScript(c,new File("schema.sql"),new PrintWriter("loadSchema.log"));
		return true;
	}

    /**
     * validates a user for login purposes
     * this uses the user_table for the user lookup
     *
     * @param username the name of the user to be validated
     * @param password the password for the specified user
     * @return whether the username/password combination is valid
     */
    public boolean validateUser(String username,String password) throws SQLException
    {
		sql = "SELECT * FROM user_table WHERE username='"+username+"' AND password='"+password+"'";
		if(debug)System.out.println("sql = " +sql);
		rs = stmt.executeQuery(sql);
		r = false;
		if(rs.next())r = true;
		if(debug)System.out.println("validateUser("+username+","+password+") = "+r+"\n");
		return r;
	}

	/**
	 * gets the next valid userid
	 * @return the next valid userid
	 */
	public int getNextUserID() throws SQLException
	{
		sql = "SELECT MAX(id) as maxid FROM user_table";
		if(debug)System.out.println("sql = " +sql);
		rs = stmt.executeQuery(sql);
		rs.next();
		int result = rs.getInt("maxid") + 1;
		if(debug)System.out.println("getNextUserId() = "+result + "\n");
		return result;
	}

    /**
     * registers a new user for the system
     * it is reccommended that getNextUserId is called directly previously to this to get the next sequential userid
     *
     * @param the profile of the player to be registered
     */
    public boolean registerUser(PlayerInfo profile) throws SQLException
    {
		if(profile.id <= 0)
		{
			System.out.println("Cannot add user. invalid userid.");
			return false;
		}
		if(usernameIsTaken(profile.username))
		{
			System.out.println("Cannot add user. Username already taken");
			return false;
		}
		//GregorianCalendar cal = new GregorianCalendar();
		//cal.setTime(profile.birth);

		try{
			sql = "INSERT INTO user_table(id, username, email, password, gender, birth) ";
			sql += " VALUES("+profile.id+",'"+profile.username+"','"+profile.email+"',";
			sql += "'"+profile.password+"','"+profile.gender+"','"+profile.birth+"')";
			//sql += cal.get(Calendar.DAY_OF_MONTH)+"";
			//if((cal.get(Calendar.MONTH)+"").length() == 1)sql += "0";
			//sql += cal.get(Calendar.MONTH)+"";
			//sql += cal.get(Calendar.YEAR)+"','DDMMYYYY'))";
			if(debug)System.out.println("sql="+sql);
			rows = stmt.executeUpdate(sql);
		}
		catch(SQLException e)
		{
			System.out.println("Error: user could not be registered\n"+e);
			return false;
		}
		if(debug)System.out.println("registerUser(profile) = " + rows + "\n");
		return (rows == 1);
	}

	/**
	 * Gets the specified user id for the username given
	 *
	 * @param username The user name for the requested user
	 * @return The id of the requested user; an id of -1 denotes an error or no user found;
	 */
	public int getUserId(String username)
	{
		int id = -1;
		try{
			sql = "SELECT id FROM user_table WHERE username='"+username+"'";
			if(debug)System.out.println("sql = "+sql);
			rs = stmt.executeQuery(sql);
			if(rs.next())id = rs.getInt("id");
			if(debug)System.out.println("getUserId("+username+") = "+id+"\n");
		}
		catch(SQLException e)
		{
			System.out.println("Error: in getUserId("+username+")\n");
			return -1;//this denotes an error
		}
		return id;
	}

    /**
     * gets a user profile so this or another player can see it
     * @param userid the unique id for the requested user
     * @return the retreived player information
     */
    public PlayerInfo getUserProfile(int userid) throws SQLException
    {
		if(userid <= 0)
		{
			System.out.println("Error in getUserProfile("+userid+"). Invalid userid");
		}
		PlayerInfo profile = new PlayerInfo();
		try{
			sql = "SELECT * FROM user_table WHERE id="+userid;
			if(debug)System.out.println("sql = "+ sql);
			rs = stmt.executeQuery(sql);
			rs.next();
			profile.username = rs.getString("username");
			profile.email = rs.getString("email");
			profile.password = rs.getString("password");
			profile.gender = rs.getString("gender");
			profile.birth = rs.getString("birth");
		}
		catch(SQLException e)
		{
			System.out.println("Error: getUserProfile("+userid+") User not found in database.\n");
			return null;
		}

		try{
			sql = "SELECT * FROM reversi_table WHERE user_id="+userid;
			if(debug)System.out.println("sql = "+ sql);
			rs = stmt.executeQuery(sql);
			rs.next();
			profile.gameStats = new GameStats[1];
			profile.gameStats[0] = new GameStats();
			profile.gameStats[0].gameName = "Reversi";
			profile.gameStats[0].wins = rs.getInt("wins");
			profile.gameStats[0].losses = rs.getInt("losses");
			profile.gameStats[0].forfeits = rs.getInt("forfeits");
		}
		catch(SQLException e)
		{
			System.out.println("Error: getUserProfile("+userid+") User not found in Reversi Game Table.\n");
			return null;
		}
		//add additional games here when made

		if(debug)System.out.println("getUserProfile("+userid+") = "+profile.toString() + "\n");
		return profile;
	}

	/**
	 * finds if the username is already taken
	 * @param the username being queried
	 * @return whether or not the username is in the database
	 */
	public boolean usernameIsTaken(String username) throws SQLException
	{
		try{
			sql = "SELECT * FROM user_table WHERE username='" + username + "'";
			if(debug)System.out.println("sql = "+ sql);
			rs = stmt.executeQuery(sql);
		}
		catch(SQLException e)
		{
			System.out.println("The username provided is invalid");
			return true;
		}
		r = false;
		if(rs.next()) r = true;
		if(debug)System.out.println("usernameIsTaken("+username+") = " + r + "\n");
		return r;
	}

    /**
     * updates a user profile in the database
     * @param profile the profile to be updated
     * @return whether the profile was successfully updated or not
     */
    public boolean updateUserProfile(PlayerInfo profile) throws SQLException
    {
		//GregorianCalendar cal = new GregorianCalendar();
		//cal.setTime(profile.birth);
		sql = "UPDATE user_table SET ";
		sql += "username='"+profile.username+"', ";
		sql += "email='"+profile.email+"', ";
		sql += "password='"+profile.password+"', ";
		sql += "gender='"+profile.gender+"', ";
		sql += "birth='"+profile.birth+"' ";
		//sql += cal.get(Calendar.DAY_OF_MONTH)+"";
		//if((cal.get(Calendar.MONTH)+"").length() == 1)sql += "0";
		//sql += cal.get(Calendar.MONTH)+"";
		//sql += cal.get(Calendar.YEAR)+"','DDMMYYY') ";
		sql += " WHERE id = " + profile.id;
		if(debug)System.out.println("sql = "+ sql);
		rows = stmt.executeUpdate(sql);
		if(debug)System.out.println("updateUserProfile(profile) = " + rows + "\n");
		return (rows == 1);
	}

	/**
	 * sees if a user is in the gametable
	 *
	 * @param tablename the name of the table to query
	 * @param userid the userid to look for
	 * @return whether or not the user is in the game table or not
	 */
	public boolean isUserInGameTable(String tablename, int userid) throws SQLException
	{
		sql = "SELECT * FROM "+tablename+" WHERE user_id="+userid;
		if(debug)System.out.println("sql = "+ sql);
		rs = stmt.executeQuery(sql);
		r = rs.next();
		if(debug)System.out.println("isUserInGameTable("+tablename+","+userid+") = "+r+"\n");
		return r;
	}

    /**
     * adds a user to a game table if they are entering it for the first time
     *
     * @param tablename the name of the table to add to
	 * @param userid the userid to look for
	 * @return whether or not the user is in the game table or not
     */
    public boolean addNewUserToGameTable(String tablename, int userid) throws SQLException
    {
		if(isUserInGameTable(tablename,userid) == true)return false;
		sql = "INSERT INTO " + tablename + " (user_id,wins,losses,forfeits) ";
		sql += "VALUES ("+userid+",0,0,0)";
		if(debug)System.out.println("sql = "+ sql);
		rows = stmt.executeUpdate(sql);
		if(debug)System.out.println("addNewUserToGameTable("+tablename+","+userid+") = " + rows + "\n");
		return (rows == 1) ;
	}

    /**
     * adds a win to a user profile
     *
     * @param tablename the name of the table to add to
	 * @param userid the userid to add to
	 * @return whether or not the add to the database was successful
     */
    public boolean addWin(String tablename, int userid) throws SQLException
    {
		r =  addField(tablename,userid,"wins");
		if(debug)System.out.println("addWin("+tablename+","+userid+") = "+r+"\n");
		return r;
	}

    /**
     * adds a loss to a user profile
     *
     * @param tablename the name of the table to add to
	 * @param userid the userid to add to
	 * @return whether or not the add to the database was successful
     */
    public boolean addLoss(String tablename, int userid) throws SQLException
    {
		r = addField(tablename,userid,"losses");
		if(debug)System.out.println("addLoss("+tablename+","+userid+") = "+r+"\n");
		return r;
	}

    /**
     * adds a forfeit to a user table
     * @param tablename the name of the table to add to
     * @param userid the userid to add to
	 * @return whether or not the add to the database was successful
     */
    public boolean addForfeit(String tablename, int userid) throws SQLException
    {
		r = addField(tablename,userid,"forfeits");
		if(debug)System.out.println("addForfeit("+tablename+","+userid+") = "+r+"\n");
		return r;
	}

    /**
     * adds a 1 to the number in a particular field
     *
     * @param tablename the name of the table to add to
	 * @param userid the userid to add to
	 * @return whether or not the add to the database was successful
     */
    public boolean addField(String tablename, int userid, String field) throws SQLException
    {
		sql = "SELECT " + field + " FROM " + tablename + " WHERE user_id=" + userid;
		if(debug)System.out.println("sql = "+sql);
		rs = stmt.executeQuery(sql);
		rs.next();
		int num = rs.getInt(field);
		if(debug)System.out.println("previous number = "+num);
		sql = "UPDATE " + tablename + " SET " + field + " = " + (num + 1) + " ";
		sql += "WHERE user_id=" + userid;
		if(debug)System.out.println("sql = "+sql);
		rows = stmt.executeUpdate(sql);
		return (rows == 1);
	}

	/**
	 * gets the high scores for a game
	 *
	 * @param tablename the name of the table to add to
	 * @param userid the userid to add to
	 * @return whether or not the add to the database was successful
	 */
	public String getHighScores(String tablename, int numplayers) throws SQLException
	{
		StringBuffer out = new StringBuffer("");

		sql = "SELECT u.username, (s.wins - s.losses - s.forfeits) as rank FROM " + tablename + " s JOIN user_table u ON s.user_id=u.id ORDER BY rank DESC";
		if(debug)System.out.println("sql = "+sql);
		rs = stmt.executeQuery(sql);
		while(rs.next() && numplayers-- > 0)
		{
			out.append(rs.getString("username")+ " - " + rs.getInt("rank") + ",");
		}
		if(debug)System.out.println("getHighScores("+tablename+","+numplayers+") = "+out.toString()+"\n");
		return out.toString();
	}

	//this ensures that the id provided is a valid one
	private boolean isValidId(int id)
	{
		return (id > 0);
	}


}//end class
